﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using IronOne.SalesOne.BusinessObjects;
using System.Linq.Dynamic;
using System.Configuration;

namespace IronOne.SalesOne.DataObjects
{
    public class LinqSuspect
    {
        public int pageSize = Convert.ToInt32(ConfigurationManager.AppSettings["GridCount"]);

        public List<Suspect> GetSuspects(string sortExpression)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IQueryable<SuspectEntity> query = db.SuspectEntities;

                if (sortExpression.Length > 0)
                {
                    string[] sort = sortExpression.Split(' ');
                    string sortColumn = sort[0];
                    string sortOrder = sort[1];

                    switch (sortColumn)
                    {
                        case "SuspectID":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.SuspectID);
                            else
                                query = query.OrderByDescending(c => c.SuspectID);
                            break;
                        case "CustomerID":
                            if (sortOrder == "ASC")
                                query = query.OrderBy(c => c.SuspectID);
                            else
                                query = query.OrderByDescending(c => c.SuspectID);
                            break;   
                    }
                }
                return query.Select(c => Mapper.ToSuspectObject(c)).ToList();
            }
        }

        public Suspect GetSuspect(string suspectId)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                return Mapper.ToSuspectObject(db.SuspectEntities
                            .SingleOrDefault(p => p.SuspectID == suspectId));
            }
        }

        public void InsertSuspect(Suspect suspect)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    SuspectEntity entity = Mapper.ToSuspectEntity(suspect);
                    db.SuspectEntities.InsertOnSubmit(entity);
                    db.SubmitChanges();

                    suspect.SuspectId= entity.SuspectID;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to suspect record was made before your changes.");
                }
                catch (Exception)
                {
                    throw new Exception("Unable to Insert contact " + suspect.SuspectId);
                }
            }
        }


        public int UpdateSuspect(Suspect suspect)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    SuspectEntity entity = Mapper.ToSuspectEntity(suspect);
                    db.SuspectEntities.Attach(entity, true);
                    db.SubmitChanges();
                    return 1;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to suspect record was made before your changes.");
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }


        public int DeleteSuspect(Suspect suspect)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                try
                {
                    SuspectEntity entity = Mapper.ToSuspectEntity(suspect);
                    db.SuspectEntities.Attach(entity, false);
                    db.SuspectEntities.DeleteOnSubmit(entity);
                    db.SubmitChanges();

                    return 1;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to suspect record was made before your changes.");
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }

        public Suspect GetSuspForCustomer(string customerId)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                return Mapper.ToSuspectObject(db.SuspectEntities
                            .SingleOrDefault(p => p.CustomerID == customerId));
            }
        }


        public List<Suspect> SearchSuspects(ContactSearch pros)
        {
            StringBuilder sb = new StringBuilder();
            if (!String.IsNullOrEmpty(pros.CliemtId))
                sb.Append(" AND CustomerID = \"" + pros.CliemtId + "\"");




            StringBuilder str = new StringBuilder();

            //unit
            if (pros.Unt !=null)
            {
                int j = 0;
                foreach (SalesOneUnit un in pros.Unt)
                {

                    if (j == 0)
                    {
                        if (un.UnitId != 0)
                            str.Append(" AND (NodeId=" + un.UnitId.ToString() + " AND UserTypeId = 4)");

                    }
                    else
                    {
                        if (un.UnitId != 0)
                            str.Append(" OR (NodeId=" + un.UnitId.ToString() + " AND UserTypeId = 4)");

                    }

                    j++;
                }
            }
            


            //branch
            if (pros.Brnch != null)
            {
                int k = 0;
                foreach (Branch br in pros.Brnch)
                {

                    if (k == 0)
                    {
                        if (br.BranchId != 0 && pros.Unt == null)
                            str.Append(" AND (NodeId=" + br.BranchId.ToString() + " AND UserTypeId = 3)");
                    }
                    else
                    {
                        if (br.BranchId != 0 && pros.Unt == null)
                            str.Append(" OR (NodeId=" + br.BranchId.ToString() + " AND UserTypeId = 3)");
                    }

                    k++;
                }
            }
            


            //so
            if (pros.SOCode !=null)
            {
                int i = 0;
                foreach (string so in pros.SOCode)
                {
                    if (i == 0)
                    {
                        if (!String.IsNullOrEmpty(so) && so != "Select")
                            sb.Append(" AND SOCode = \"" + so + "\"");
                    }
                    else
                    {
                        if (!String.IsNullOrEmpty(so) && so != "Select")
                            sb.Append(" OR SOCode = \"" + so + "\"");
                    }

                    i++;
                }
            }
            





            StringBuilder cus = new StringBuilder();
            if (!String.IsNullOrEmpty(pros.Name))
                cus.Append(" AND FirstName = \"" + pros.Name + "\"");
            if (!String.IsNullOrEmpty(pros.IdNo) && !String.IsNullOrEmpty(pros.IdType))
                cus.Append("IDNumber = \"" + pros.IdNo + "\"" + " AND IDType = " + pros.IdType);

            if (!String.IsNullOrEmpty(pros.Contact))
                cus.Append(" AND ContactNo = " + pros.Contact);


            if (sb.Length > 1 || cus.Length > 1 || str.Length > 1)
            {
                if (sb.ToString().StartsWith(" AND "))
                    sb.Remove(0, 5);
                if (cus.ToString().StartsWith(" AND "))
                    cus.Remove(0, 5);
                if (str.ToString().StartsWith(" AND "))
                    str.Remove(0, 5);


                using (SODataDataContext db = DataContextFactory.CreateContext())
                {
                    IQueryable<SuspectEntity> query = db.SuspectEntities;

                    if (str.Length > 1)
                    {
                        if (sb.Length > 1 && cus.Length > 1)
                        {
                            query = from pol in (db.SuspectEntities.Where(sb.ToString()))
                                    join cust in
                                        (from c in db.CustomerEntities.Where(cus.ToString())
                                         join u in db.UserEntities on c.SOCode equals u.SOCode
                                         join us in db.UsersInStructureEntities.Where(str.ToString())
                                         on u.UserId equals us.UserId
                                         select c)

                                    on pol.CustomerID equals cust.CustomerID
                                    select pol;
                        }
                        else if (sb.Length > 1 && cus.Length < 1)
                        {
                            query = from pol in (db.SuspectEntities.Where(sb.ToString()))
                                    join cust in
                                        (from c in db.CustomerEntities
                                         join u in db.UserEntities on c.SOCode equals u.SOCode
                                         join us in db.UsersInStructureEntities.Where(str.ToString())
                                         on u.UserId equals us.UserId
                                         select c)
                                    on pol.CustomerID equals cust.CustomerID
                                    select pol;
                        }
                        else if (sb.Length < 1 && cus.Length > 1)
                        {
                            query = from pol in db.SuspectEntities
                                    join cust in
                                        (from c in db.CustomerEntities.Where(cus.ToString())
                                         join u in db.UserEntities on c.SOCode equals u.SOCode
                                         join us in db.UsersInStructureEntities.Where(str.ToString())
                                         on u.UserId equals us.UserId
                                         select c)
                                    on pol.CustomerID equals cust.CustomerID
                                    select pol;
                        }
                        else if (sb.Length < 1 && cus.Length < 1)
                        {
                            query = from pol in db.SuspectEntities
                                    join cust in
                                        (from c in db.CustomerEntities
                                         join u in db.UserEntities on c.SOCode equals u.SOCode
                                         join us in db.UsersInStructureEntities.Where(str.ToString())
                                         on u.UserId equals us.UserId
                                         select c)
                                    on pol.CustomerID equals cust.CustomerID
                                    select pol;
                        }
                    }
                    else if (str.Length < 1)
                    {
                        if (sb.Length > 1 && cus.Length > 1)
                        {
                            query = from pol in (db.SuspectEntities.Where(sb.ToString()))
                                    join cust in (db.CustomerEntities.Where(cus.ToString()))
                                    on pol.CustomerID equals cust.CustomerID
                                    select pol;
                        }
                        else if (sb.Length > 1 && cus.Length < 1)
                        {
                            query = from pol in (db.SuspectEntities.Where(sb.ToString()))
                                    join cust in db.CustomerEntities
                                    on pol.CustomerID equals cust.CustomerID
                                    select pol;
                        }
                        else if (sb.Length < 1 && cus.Length > 1)
                        {
                            query = from pol in db.SuspectEntities
                                    join cust in (db.CustomerEntities.Where(cus.ToString()))
                                    on pol.CustomerID equals cust.CustomerID
                                    select pol;
                        }
                    }



                    return query.OrderBy(c => c.SOCode).Select(c => Mapper.ToSuspectObject(c)).ToList();
                }
            }
            else
                return null;
        }



        public List<Customer> GetSuspectsForBranch(List<Branch> brnch,int pageNo)
        {

            try
            {
                StringBuilder str = new StringBuilder();
                string status = "Suspect";
                str.Append("Status = \"" + status + "\"");

                if (brnch != null && brnch.Count >0 )
                {

                    int k = 0;
                    foreach (Branch br in brnch)
                    {

                        if (k == 0)
                        {
                            if (br.BranchId != 0)
                                str.Append(" AND BranchId=" + br.BranchId.ToString());
                        }
                        else
                        {
                            if (br.BranchId != 0)
                                str.Append(" OR BranchId=" + br.BranchId.ToString());
                        }

                        k++;
                    }


                    using (SODataDataContext db = DataContextFactory.CreateContext())
                    {

                        IQueryable<CustomerEntity> query = db.CustomerEntities;
                        query = (from cus in db.CustomerEntities.Where(str.ToString())
                                 select cus).OrderBy(c => c.SOCode);


                        IQueryable<CustomerEntity> pageData = query.Skip((pageNo - 1) * pageSize).Take(pageSize);

                        return pageData.Select(c => Mapper.ToCustomerObject(c)).ToList();
                    }
                }
                else
                {
                    return null;
                }
                
            }
            catch (Exception)
            {
                return null;
                
            }

            
        
        }











    }
}
